﻿Imports System.Data.SqlClient

Partial Public Class UCBaseData
    Inherits System.Web.UI.UserControl
    Dim treLeftPath As String
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BuildMainTree()
            DataBound()
            PnlCode.Visible = False
            PnlData.Visible = False
            PnlTree.Visible = False
            PnlUDFDesign.Visible = False
            PnlUserDefine.Visible = False
        End If
    End Sub
    Private Sub BuildMainTree()
        Dim dr As SqlDataReader
        dr = SqlHelper.ExecuteReader("SELECT [ID],[TableName],[TableType],[TableTitle] FROM [toMyCommBaseDataTables] ORDER BY ID ASC")
        Dim treRoot As New TreeNode
        treMenu.Nodes.Clear()
        treRoot.Text = "基础数据维护"
        treRoot.Value = "root"
        treMenu.Nodes.Add(treRoot)
        treRoot.Expand()
        While dr.Read()
            Dim treNode As New TreeNode
            treNode.Text = dr("TableTitle")
            treNode.Value = dr("TableType").ToString + dr("TableName").ToString
            treRoot.ChildNodes.Add(treNode)
        End While
        dr.Close()
    End Sub
    Protected Sub treMenu_SelectedNodeChanged(ByVal sender As Object, ByVal e As EventArgs) Handles treMenu.SelectedNodeChanged
        Dim strType As String
        Dim strTable As String
        Dim strTitle As String
        Dim tmpTree As New TreeView
        tmpTree = CType(sender, TreeView)
        strType = Left(tmpTree.SelectedNode.Value, 1)
        strTable = Mid(tmpTree.SelectedNode.Value, 2)
        hidTableName.Value = strTable
        strTitle = tmpTree.SelectedNode.Text
        If tmpTree.SelectedNode.Value = "root" Then
            pnlMain.Visible = True
            pnlMainCreate.Visible = True
            PnlCode.Visible = False
            PnlData.Visible = False
            PnlTree.Visible = False
            PnlUDFDesign.Visible = False
            PnlUserDefine.Visible = False
            Return
        End If
        Select Case strType
            Case "1"
                pnlMain.Visible = False
                PnlData.Visible = True
                PnlCode.Visible = False
                PnlTree.Visible = False
                PnlUDFDesign.Visible = False
                PnlUserDefine.Visible = False
                BuildData()
                txtDataID.Text = ""
                txtDataName.Text = ""
            Case "2"
                pnlMain.Visible = False
                PnlData.Visible = False
                PnlCode.Visible = True
                PnlTree.Visible = False
                PnlUDFDesign.Visible = False
                PnlUserDefine.Visible = False

                BuildCode()
                txtCodeID.Text = ""
                txtCodeNO.Text = ""
                txtCodeName.Text = ""
            Case "3"
                pnlMain.Visible = False
                PnlData.Visible = False
                PnlCode.Visible = False
                PnlTree.Visible = True
                PnlUDFDesign.Visible = False
                PnlUserDefine.Visible = False
                Dim tdRoot As New TreeNode
                treLeft.Nodes.Clear()
                tdRoot.Text = strTitle
                tdRoot.Value = "0_" & strTable
                treLeft.Nodes.Add(tdRoot)
                tdRoot.Expand()

                BuildRecursiveTree(tdRoot, strTable)

                txtTreParentNo.Text = ""
                txtTreCurrentNo.Text = ""
                txtTreTitle.Text = ""
                txtTreOrder.Text = ""
                lblMsgTreeType.Text = ""
            Case "4"
                pnlMain.Visible = False
                PnlData.Visible = False
                PnlCode.Visible = False
                PnlTree.Visible = False
                PnlUDFDesign.Visible = False
                PnlUserDefine.Visible = True
                BuildUDF()
        End Select

    End Sub
    ''' <summary>
    ''' 将指定字符串按指定长度进行剪切
    ''' </summary>
    ''' <param name="strOld">需要截断的字符串</param>
    ''' <param name="maxLength">字符串的最大长度 </param>
    ''' <param name="strEnd">超过长度的后缀</param>
    ''' <returns>如果超过长度，返回截断后的新字符串加上后缀，否则，返回原字符串</returns>
    ''' <remarks></remarks>
    Public Function StringTruncat(ByVal strOld As String, ByVal maxLength As Integer, ByVal strEnd As String) As String
        Dim strTemp As String
        If String.IsNullOrEmpty(strOld) Then
            StringTruncat = ""
        Else
            If maxLength < 1 Then
                Throw New Exception("返回的字符串长度必须大于[0]")
            End If
            If strOld.Length > maxLength Then
                strTemp = strOld.Substring(0, maxLength)
                If Not String.IsNullOrEmpty(strEnd) Then
                    strTemp = strTemp + strEnd
                End If
            Else
                strTemp = strOld
            End If
            StringTruncat = strTemp
        End If
    End Function
    Private Sub BuildData()
        Dim ds As DataSet
        ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " ORDER BY ID")
        gvData.DataSource = ds
        gvData.DataBind()
    End Sub
    Private Sub BuildCode()
        Dim ds As DataSet
        ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " ORDER BY ID ")
        gvCode.DataSource = ds
        gvCode.DataBind()
    End Sub
    ''' <summary>
    ''' 绑定用户自定义模块，不需要设置显示文本框
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub BuildUDF2()
        Dim ds As DataSet
        'ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " Order by ident")
        ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " ORDER BY IDENT")
        gvUDF.DataSource = ds
        gvUDF.DataBind()
    End Sub
    ''' <summary>
    ''' 首次绑定用户自定义模块，根据字段数设定显示哪些文本框,第二次绑定时不再需要
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub BuildUDF()
        Dim ds As DataSet
        Dim iColumn As Integer
        'ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " Order by ident")
        ds = SqlHelper.ExecuteDataset("select * from " & hidTableName.Value & " ORDER BY IDENT")
        If ds.Tables.Count > 0 Then
            gvUDF.DataSource = ds
            gvUDF.DataBind()
            iColumn = ds.Tables(0).Columns.Count
            'If iColumn <= 4 Then
            '    UDFTable.Rows(1).Visible = False
            '    UDFTable.Rows(2).Visible = False
            'ElseIf iColumn <= 8 Then
            '    UDFTable.Rows(2).Visible = False
            'Else
            'End If
            txtUDF2.Visible = False
            txtUDF3.Visible = False
            txtUDF4.Visible = False
            txtUDF5.Visible = False
            txtUDF6.Visible = False
            txtUDF7.Visible = False
            txtUDF8.Visible = False
            txtUDF9.Visible = False
            txtUDF10.Visible = False
            txtUDF11.Visible = False
            txtUDF12.Visible = False
            If iColumn > 1 Then
                txtUDF2.Visible = True
                txtUDF2.Text = ""
            End If
            If iColumn > 2 Then
                txtUDF3.Visible = True
                txtUDF3.Text = ""
            End If
            If iColumn > 3 Then
                txtUDF4.Visible = True
                txtUDF4.Text = ""
            End If
            If iColumn > 4 Then
                txtUDF5.Visible = True
                txtUDF5.Text = ""
            End If
            If iColumn > 5 Then
                txtUDF6.Visible = True
                txtUDF6.Text = ""
            End If
            If iColumn > 6 Then
                txtUDF7.Visible = True
                txtUDF7.Text = ""
            End If
            If iColumn > 7 Then
                txtUDF8.Visible = True
                txtUDF8.Text = ""
            End If
            If iColumn > 8 Then
                txtUDF9.Visible = True
                txtUDF9.Text = ""
            End If
            If iColumn > 9 Then
                txtUDF10.Visible = True
                txtUDF10.Text = ""
            End If
            If iColumn > 10 Then
                txtUDF11.Visible = True
                txtUDF11.Text = ""
            End If
            If iColumn > 11 Then
                txtUDF12.Visible = True
                txtUDF12.Text = ""
            End If

        Else

        End If
    End Sub
    ''' <summary>
    ''' 绑定用户自定义类型数据格式
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub BuildUDFTemp()
        Dim table As New DataTable()
        Dim column As DataColumn
        ' Create first column and add to the DataTable.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "ID"
        column.AutoIncrementSeed = 1
        column.AutoIncrementStep = 1
        column.AutoIncrement = True
        column.Caption = "ID"
        column.ReadOnly = True
        column.Unique = True
        ' Add the column to the DataColumnCollection.
        table.Columns.Add(column)
        ' Create second column.
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.String")
        column.ColumnName = "FieldName"
        column.AutoIncrement = False
        column.Caption = "FieldName"
        column.ReadOnly = False
        column.Unique = True
        table.Columns.Add(column)
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "FieldType"
        column.AutoIncrement = False
        column.Caption = "FieldType"
        column.ReadOnly = False
        column.Unique = False
        table.Columns.Add(column)
        column = New DataColumn()
        column.DataType = System.Type.GetType("System.Int32")
        column.ColumnName = "ISKey"
        column.AutoIncrement = False
        column.Caption = "ISKey"
        column.ReadOnly = False
        column.Unique = False
        table.Columns.Add(column)
        gvUDFTemp.DataSource = table
        gvUDFTemp.DataBind()
        Session.Add("UDFTable", table)

    End Sub
    ''' <summary>
    ''' 创建递归树
    ''' </summary>
    ''' <param name="ParentNode">父节点</param>
    ''' <param name="tableName">数据表名</param>
    ''' <remarks></remarks>
    Private Sub BuildRecursiveTree(ByVal ParentNode As TreeNode, ByVal tableName As String)
        Dim nd As TreeNode
        Dim dr As SqlDataReader
        Dim strSql As String
        strSql = "SELECT [ID],[ParentID],[Name],[SortID] FROM [" & tableName & "] where ParentID=" + Split(ParentNode.Value, "_")(0)
        Debug.Print(strSql)
        dr = SqlHelper.ExecuteReader(strSql)
        While dr.Read
            nd = New TreeNode
            nd.Text = dr("Name")
            nd.Value = dr("ID").ToString + "_" + dr("SortID").ToString
            ParentNode.ChildNodes.Add(nd)
            BuildRecursiveTree(nd, tableName)
        End While
        dr.Close()
    End Sub
    Private Sub DataBound()
        Dim ds As DataSet
        ds = SqlHelper.ExecuteDataset("SELECT * FROM [toMyCommBaseDataTables] ORDER BY ID ASC")
        gvMain.DataSource = ds
        gvMain.DataBind()
    End Sub
    Protected Sub cmdJumpPage_Click(ByVal sender As Object, ByVal e As EventArgs)
        gvMain.PageIndex = Integer.Parse(CType(gvMain.BottomPagerRow.FindControl("txtNewPageIndex"), TextBox).Text) - 1
        DataBound()
    End Sub
    Protected Sub cmdJumpPageCode_Click(ByVal sender As Object, ByVal e As EventArgs)
        gvCode.PageIndex = Integer.Parse(CType(gvCode.BottomPagerRow.FindControl("txtNewPageIndex"), TextBox).Text) - 1
        BuildCode()
    End Sub
    Protected Sub cmdJumpPageData_Click(ByVal sender As Object, ByVal e As EventArgs)
        gvData.PageIndex = Integer.Parse(CType(gvData.BottomPagerRow.FindControl("txtNewPageIndex"), TextBox).Text) - 1
        BuildData()
    End Sub
    Protected Sub cmdJumpPageUDF_Click(ByVal sender As Object, ByVal e As EventArgs)
        gvUDF.PageIndex = Integer.Parse(CType(gvUDF.BottomPagerRow.FindControl("txtNewPageIndex"), TextBox).Text) - 1
        BuildData()
    End Sub
    Protected Sub gvMain_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvMain.PageIndexChanging
        If e.NewPageIndex >= 0 And e.NewPageIndex < gvMain.PageCount Then
            gvMain.PageIndex = e.NewPageIndex
            DataBound()
        End If
    End Sub
    Protected Sub lbnDel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim strTableName As String
        strTableName = CType(sender, LinkButton).CommandArgument
        'iRet = SqlHelper.ExecuteNonQuery(strConn, CommandType.Text, "delete from ")
        'ds = SqlHelper.ExecuteDataset(strConn, CommandType.Text, "select * from toMyCommBaseDataTables")
    End Sub
    ''' <summary>
    ''' 基础数据表格按钮事件
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub gvMain_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvMain.RowCommand
        Dim strSql As String
        Dim gvrow As GridViewRow
        'index = gvrow.RowIndex
        Select Case e.CommandName
            Case "select"
                gvrow = CType(sender, GridView).Rows(e.CommandArgument)
                txtID.Text = CType(gvrow.FindControl("lblID"), Label).Text
                txtTableName.Text = CType(gvrow.FindControl("lblName"), Label).Text
                txtTableTitle.Text = CType(gvrow.FindControl("lblTitle"), Label).ToolTip
                ddlTableType.SelectedValue = CType(gvrow.FindControl("lblType"), Label).Text
            Case "del"
                Dim sqlCommand As New List(Of String)
                Dim id As String
                Dim strName As String
                gvrow = CType(sender, GridView).Rows(e.CommandArgument)
                id = CType(gvrow.FindControl("lblID"), Label).Text
                strName = CType(gvrow.FindControl("lblName"), Label).Text
                strSql = "DELETE FROM toMyCommBaseDataTables WHERE ID=" + id
                sqlCommand.Add(strSql)
                strSql = "Drop Table " + strName
                sqlCommand.Add(strSql)
                'Throw (New Exception("暂时不执行删除操作"))
                If SqlHelper.ExecuteSqlTran(sqlCommand) > 0 Then
                    DataBound()
                    BuildMainTree()
                End If
        End Select
    End Sub
    ''' <summary>
    ''' 创建表
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub btnCreate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnCreate.Click
        Dim iRet As Integer
        Dim strInsertSql As String
        Dim strTableTitle As String
        Dim strTableName As String
        Dim strTableType As String
        Dim strSql As String
        strTableName = txtTableName.Text.Trim
        hidTableName.Value = strTableName
        strTableTitle = txtTableTitle.Text.Trim
        strTableType = ddlTableType.SelectedValue
        iRet = SqlHelper.TabExists(strTableName)
        If iRet = 0 Then    '数据库中没有这张表
            strInsertSql = "INSERT INTO [toMyCommBaseDataTables]([TableName], [TableType], [TableTitle]) VALUES( '"
            strInsertSql = strInsertSql + strTableName + "'," + strTableType + ",'" + strTableTitle + "')"
            Select Case ddlTableType.SelectedValue
                Case 1          '数据类型
                    strSql = "CREATE TABLE " & strTableName & " ([ID] [int] IDENTITY (1, 1) NOT NULL primary key,[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ) "
                Case 2          '编码类型
                    strSql = "CREATE TABLE " & strTableName & " ([ID] [int] IDENTITY (1, 1) NOT NULL,[CodeID] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL  primary key,[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL )"
                Case 3          '树状类型
                    strSql = "CREATE TABLE " & strTableName & " ([ID] [int] IDENTITY (1, 1) NOT NULL ,[ParentID] [int] NOT NULL ,[Name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,[SortID] [int] NOT NULL )"
                Case 4          '自定义类型
                    pnlMainCreate.Visible = False
                    PnlUDFDesign.Visible = True
                    BuildUDFTemp()
                    Return
                Case Else
                    Return
            End Select
            Dim strCommand As New List(Of String)
            strCommand.Add(strInsertSql)
            strCommand.Add(strSql)
            iRet = SqlHelper.ExecuteSqlTran(strCommand)
            If iRet > 0 Then
                treMenu.Nodes(0).ChildNodes.Add(New TreeNode(strTableTitle, ddlTableType.SelectedValue + strTableName))
                DataBound()
            End If
        Else
            MsgBox("数据库中已经存在表[ " & strTableName & " ]" & Chr(10) & Chr(13) & "请更改为其他未使用的表名 ", MsgBoxStyle.OkOnly, "注意")
            txtTableName.Focus()
            Return
        End If
    End Sub

    Protected Sub btnUpdata_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdata.Click
        Dim strSql As String
        Dim strTableTitle As String
        Dim strTableName As String
        strTableName = txtTableName.Text.Trim
        strTableTitle = txtTableTitle.Text.Trim
        If Not String.IsNullOrEmpty(strTableTitle) Then
            strSql = "update toMyCommBaseDataTables set TableTitle='" & strTableTitle & "' Where TableName='" & strTableName & "' AND ID=" & Integer.Parse(txtID.Text)
            If SqlHelper.ExecuteSql(strSql) > 0 Then
                DataBound()
                BuildMainTree()
                'Response.Write("<script>alert('通过');</script>")
                MsgBox("ss", MsgBoxStyle.Critical, "title")
            End If
        End If
    End Sub

    Protected Sub treLeft_SelectedNodeChanged(ByVal sender As Object, ByVal e As EventArgs) Handles treLeft.SelectedNodeChanged
        Dim tmpString() As String
        Dim tmpTree As New TreeView
        tmpTree = CType(sender, TreeView)
        hidFullPath.Value = tmpTree.SelectedNode.ValuePath
        tmpString = Split(tmpTree.SelectedNode.Value, "_")
        txtTreCurrentNo.Text = tmpString(0)
        If tmpString(0) = "0" Then '根节点
            txtTreTitle.Text = tmpTree.SelectedNode.Text
            txtTreParentNo.Text = "0"
            txtTreOrder.Text = "0"
            txtTreParentNo.Text = "0"
        Else
            txtTreTitle.Text = tmpTree.SelectedNode.Text
            txtTreOrder.Text = tmpString(1)
            txtTreParentNo.Text = Split(tmpTree.SelectedNode.Parent.Value, "_")(0)
        End If
        lblMsgTreeType.Text = ""
    End Sub

    Protected Sub btnTreAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTreAdd.Click
        Dim tdParentNode As New TreeNode
        Dim tdAdd As New TreeNode
        Dim strSql As String
        Dim iRet As Integer
        lblMsgTreeType.Text = ""
        tdParentNode = treLeft.FindNode(hidFullPath.Value)
        strSql = "Insert Into [" & hidTableName.Value & "] (ParentID,Name,SortID) " & "values(" & txtTreCurrentNo.Text.Trim & ",'" & txtTreTitle.Text.Trim & "'," & (Integer.Parse(txtTreOrder.Text)).ToString & ")"
        iRet = SqlHelper.ExecuteInsertId(strSql, hidTableName.Value)
        If (iRet > 0) Then
            tdAdd.Text = txtTreTitle.Text.Trim
            tdAdd.Value = iRet.ToString + "_" + txtTreOrder.Text.Trim
            tdParentNode.ChildNodes.Add(tdAdd)
            lblMsgTreeType.Text = "插入成功"
        Else
            lblMsgTreeType.Text = "插入错误"
        End If

    End Sub

    Protected Sub btnTreUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTreUpdate.Click
        Dim tdCurrentNode As New TreeNode
        Dim tdAdd As New TreeNode
        Dim strSql As String
        Dim iRet As Integer

        tdCurrentNode = treLeft.FindNode(hidFullPath.Value)
        strSql = "UPDATE [" & hidTableName.Value & "] SET [Name] = '" & _
         txtTreTitle.Text.Trim & "',[SortID] =" & (Integer.Parse(txtTreOrder.Text)).ToString & _
         " WHERE ID=" & txtTreCurrentNo.Text
        Debug.Print(strSql)
        iRet = SqlHelper.ExecuteSql(strSql)
        If iRet > 0 Then
            If tdCurrentNode Is Nothing Then
                Return
            End If
            tdCurrentNode.Text = txtTreTitle.Text.Trim
            tdCurrentNode.Value = txtTreCurrentNo.Text.Trim + "_" + txtTreOrder.Text.Trim
            lblMsgTreeType.Text = "更新成功"
        Else
            lblMsgTreeType.Text = "更新失败"
        End If
    End Sub

    Protected Sub btnTreDel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnTreDel.Click
        Dim tdCurrentNode As New TreeNode
        Dim tdParentNode As New TreeNode
        Dim strSql As String
        Dim iRet As Integer
        tdCurrentNode = treLeft.FindNode(hidFullPath.Value)
        tdParentNode = tdCurrentNode.Parent
        If tdCurrentNode.ChildNodes.Count <> 0 And tdParentNode IsNot Nothing Then '当前节点没有子节点
            lblMsgTreeType.Text = "请选择删除子节点"
            Return
        End If
        strSql = "DELETE FROM  [" & hidTableName.Value & "] WHERE  ID=" & txtTreCurrentNo.Text
        Debug.Print(strSql)
        iRet = SqlHelper.ExecuteSql(strSql)
        If iRet > 0 Then
            tdParentNode.ChildNodes.Remove(tdCurrentNode)
            txtTreTitle.Text = ""
            txtTreOrder.Text = ""
        Else
            lblMsgTreeType.Text = "删除失败"
            Return
        End If
        If tdParentNode.ChildNodes.Count > 0 Then
            tdCurrentNode = tdParentNode.ChildNodes(0)
            txtTreTitle.Text = tdCurrentNode.Text
            txtTreCurrentNo.Text = Split(tdCurrentNode.Value, "_")(0)
            txtTreOrder.Text = Split(tdCurrentNode.Value, "_")(1)
            hidFullPath.Value = tdCurrentNode.ValuePath
        ElseIf tdParentNode.ValuePath <> "0" Then
            txtTreTitle.Text = tdParentNode.Text
            txtTreCurrentNo.Text = Split(tdParentNode.Value, "_")(0)
            txtTreOrder.Text = Split(tdParentNode.Value, "_")(1)
            hidFullPath.Value = tdParentNode.ValuePath
        End If
        lblMsgTreeType.Text = "删除成功"
    End Sub
    ''' <summary>
    ''' 数据类型按钮跳转
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub gvData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvData.PageIndexChanging
        If e.NewPageIndex >= 0 And e.NewPageIndex < gvData.PageCount Then
            gvData.PageIndex = e.NewPageIndex
            BuildData()
        End If
    End Sub

    Private Sub gvData_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvData.RowCommand
        Dim strSql As String
        Dim iRet As Integer
        Select Case e.CommandName
            Case "select"
                txtDataID.Text = gvData.Rows(e.CommandArgument).Cells(0).Text
                txtDataName.Text = gvData.Rows(e.CommandArgument).Cells(1).Text
            Case "del"
                strSql = "DELETE FROM " & hidTableName.Value & " WHERE ID=" & gvData.Rows(e.CommandArgument).Cells(0).Text
                iRet = SqlHelper.ExecuteSql(strSql)
                If iRet > 0 Then
                    txtDataID.Text = ""
                    txtDataName.Text = ""
                    BuildData()
                End If
        End Select
    End Sub

    Protected Sub btnDataAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDataAdd.Click
        Dim strSql As String
        Dim iRet As Integer
        strSql = "INSERT INTO " & hidTableName.Value & " VALUES ('" & txtDataName.Text.Trim & "')"
        iRet = SqlHelper.ExecuteInsertId(strSql, hidTableName.Value)
        If iRet > 0 Then
            BuildData()
            txtDataID.Text = iRet
        End If
    End Sub

    Protected Sub btnDataUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDataUpdate.Click
        Dim strSql As String
        Dim iRet As Integer
        strSql = "UPDATE " & hidTableName.Value & " SET NAME='" & txtDataName.Text.Trim & "' WHERE ID=" & txtDataID.Text
        iRet = SqlHelper.ExecuteSql(strSql)
        If iRet > 0 Then
            BuildData()
        End If
    End Sub

    Private Sub btnCodeUpdate_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCodeUpdate.Click
        Dim strSql As String
        Dim iRet As Integer
        '检查是否有编码与此次修改的编码相同
        strSql = "SELECT count(*) from [" & hidTableName.Value & "] WHERE CodeID='" + txtCodeNO.Text.Trim & "' AND ID<>" & txtCodeID.Text.Trim
        If SqlHelper.Exists(strSql) Then
            txtCodeNO.Focus()
            Return
        End If
        strSql = "UPDATE [" & hidTableName.Value & "] SET codeid='" + txtCodeNO.Text.Trim + "', name='" & txtCodeName.Text.Trim & "' WHERE ID=" + txtCodeID.Text.Trim
        iRet = SqlHelper.ExecuteSql(strSql)
        If iRet > 0 Then
            BuildCode()
        End If
    End Sub

    Private Sub btnCodeAdd_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCodeAdd.Click
        Dim strSql As String
        Dim iRet As Integer

        '检查是否有编码与此次修改的编码相同
        strSql = "SELECT count(*) from [" & hidTableName.Value & "] WHERE CodeID='" + txtCodeName.Text.Trim & "'"
        iRet = SqlHelper.Exists(strSql)
        If iRet <> 0 Then
            txtCodeNO.Focus()
            Return
        End If
        strSql = "INSERT INTO " & hidTableName.Value & "(CodeID,Name) VALUES ('" & txtCodeNO.Text.Trim & "','" & txtCodeName.Text.Trim & "')"

        iRet = SqlHelper.ExecuteInsertId(strSql, hidTableName.Value)
        If iRet > 0 Then
            BuildCode()
            txtCodeID.Text = iRet
        End If
    End Sub
    ''' <summary>
    ''' 编码类型按钮跳转
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub gvCode_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvCode.PageIndexChanging
        If e.NewPageIndex >= 0 And e.NewPageIndex < gvCode.PageCount Then
            gvCode.PageIndex = e.NewPageIndex
            BuildCode()
        End If
    End Sub

    Private Sub gvCode_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvCode.RowCommand
        Dim strSql As String
        Dim iRet As Integer
        Select Case e.CommandName
            Case "select"
                txtCodeID.Text = gvCode.Rows(e.CommandArgument).Cells(0).Text
                txtCodeNO.Text = gvCode.Rows(e.CommandArgument).Cells(1).Text
                txtCodeName.Text = gvCode.Rows(e.CommandArgument).Cells(2).Text
            Case "del"
                strSql = "DELETE FROM " & hidTableName.Value & " WHERE ID=" & gvCode.Rows(e.CommandArgument).Cells(0).Text
                iRet = SqlHelper.ExecuteSql(strSql)
                If iRet > 0 Then
                    txtCodeID.Text = ""
                    txtCodeNO.Text = ""
                    txtCodeName.Text = ""
                    BuildCode()
                End If
        End Select
    End Sub

    Protected Sub btnFieldAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFieldAdd.Click
        Dim strFieldName As String
        strFieldName = txtFieldName.Text.Trim
        '检查是否有编码与此次修改的编码相同
        Dim table As New DataTable
        Dim row As DataRow
        table = CType(Session("UDFTable"), DataTable)
        If table Is Nothing Or strFieldName.ToUpper = "IDENT" Or table.Select("FieldName='" & strFieldName & "'").Length > 0 Then
            Return
        End If
        '最多12个字段
        If table.Rows.Count = 12 Then
            Return
        End If
        row = table.NewRow
        row("FieldName") = txtFieldName.Text.Trim
        row("FieldType") = ddlFieldType.SelectedValue
        row("ISKey") = ddlFieldIsKey.SelectedValue
        table.Rows.Add(row)
        'BuildUDFTemp()
        txtCodeID.Text = row("ID")
        gvUDFTemp.DataSource = table
        gvUDFTemp.DataBind()
        Session.Add("UDFTable", table)
    End Sub

    Protected Sub btnFieldEdit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFieldEdit.Click
        Dim table As New DataTable
        Dim strFieldId As String
        Dim strFieldName As String
        strFieldId = txtFieldID.Text.Trim
        strFieldName = txtFieldName.Text.Trim
        table = CType(Session("UDFTable"), DataTable)
        If table Is Nothing Or "IDENT" = strFieldName.ToUpper Or "" = strFieldId Or "" = strFieldName Then
            Return
        End If
        Dim row As DataRow
        Dim expression As String
        expression = "ID<>" & strFieldId & " and FieldName='" & strFieldName & "'"
        If table.Select(expression).Length > 0 Then
            Return
        End If
        row = (table.Select("ID=" & strFieldId))(0)
        row("FieldName") = strFieldName
        row("FieldType") = ddlFieldType.SelectedValue
        row("ISKey") = ddlFieldIsKey.SelectedValue
        'table.Rows.RemoveAt()
        'strSql = "DELETE FROM toMycommUDFDesignTemp  WHERE ID=" & gvUDFTemp.Rows(e.CommandArgument).Cells(0).Text
        txtFieldID.Text = ""
        txtFieldName.Text = ""
        ddlFieldIsKey.SelectedIndex = 0
        ddlFieldType.SelectedIndex = 0
        gvUDFTemp.DataSource = table
        gvUDFTemp.DataBind()
        Session("UDFTable") = table
    End Sub
    Private Sub gvUDFTemp_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvUDFTemp.RowCommand

        Select Case e.CommandName
            Case "select"
                txtFieldID.Text = gvUDFTemp.Rows(e.CommandArgument).Cells(0).Text
                txtFieldName.Text = gvUDFTemp.Rows(e.CommandArgument).Cells(1).Text
                ddlFieldType.SelectedValue = gvUDFTemp.Rows(e.CommandArgument).Cells(2).Text
                ddlFieldIsKey.SelectedValue = gvUDFTemp.Rows(e.CommandArgument).Cells(3).Text
            Case "del"
                Dim table As New DataTable
                table = CType(Session("UDFTable"), DataTable)
                If table Is Nothing Then
                    Return
                End If
                table.Rows.RemoveAt(e.CommandArgument)
                'strSql = "DELETE FROM toMycommUDFDesignTemp  WHERE ID=" & gvUDFTemp.Rows(e.CommandArgument).Cells(0).Text

                txtFieldID.Text = ""
                txtFieldName.Text = ""
                ddlFieldIsKey.SelectedIndex = 0
                ddlFieldType.SelectedIndex = 0

                gvUDFTemp.DataSource = table
                gvUDFTemp.DataBind()
        End Select
    End Sub

    Protected Sub btnDesignFinish_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDesignFinish.Click
        Dim strSql As New StringBuilder
        Dim i As Integer
        Dim iRet As Integer
        Dim gvrow As GridViewRow
        strSql.Append("CREATE TABLE [").Append(hidTableName.Value).Append("] (IDENT bigint IDENTITY(1,1),")
        For i = 0 To gvUDFTemp.Rows.Count - 1
            gvrow = gvUDFTemp.Rows(i)
            '1.字段名
            strSql.Append(gvrow.Cells(1).Text)
            '2.字段类型
            Select Case gvrow.Cells(2).Text
                Case 0
                    strSql.Append(" [bigint] ")
                Case 1
                    strSql.Append(" [nVarChar] (200)  COLLATE Chinese_PRC_CI_AS ")
                Case 2
                    strSql.Append(" [datetime] ")
            End Select
            '3.是否为主键
            If gvrow.Cells(3).Text = "1" Then
                strSql.Append(" primary key ")
            Else
            End If
            '4
            strSql.Append(",")
        Next
        Debug.Print(strSql.ToString)
        strSql.Remove(strSql.Length - 1, 1)
        strSql.Append(")")
        Dim strInsertSql As String
        strInsertSql = "INSERT INTO [toMyCommBaseDataTables]([TableName], [TableType], [TableTitle]) VALUES( '"
        strInsertSql = strInsertSql + hidTableName.Value + "',4,'" + txtTableTitle.Text.Trim + "')"
        Dim strCommand As New List(Of String)
        strCommand.Add(strInsertSql)
        strCommand.Add(strSql.ToString)
        iRet = SqlHelper.ExecuteSqlTran(strCommand)
        If iRet > 0 Then
            treMenu.Nodes(0).ChildNodes.Add(New TreeNode(txtTableTitle.Text.Trim, "4" + hidTableName.Value))
        End If
    End Sub
    Protected Sub btnUDFAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUDFAdd.Click
        Dim strSql As String
        Dim iColumn As Integer
        Dim i As Integer
        strSql = "Select ORDINAL_POSITION,DATA_TYPE,IS_NULLABLE from information_schema.columns where table_name='" & hidTableName.Value & "' Order by ORDINAL_POSITION"
        Dim strInsert As New StringBuilder
        Dim table = SqlHelper.ExecuteDataset(strSql).Tables(0)
        strInsert.Append("Insert into ").Append(hidTableName.Value).Append(" VALUES( ")
        'Dim dr = SqlHelper.ExecuteReader(strSql)
        iColumn = table.Rows.Count
        For i = 1 To iColumn - 1
            Select Case table.Rows(i)(1).ToString()
                Case "bigint", "int"
                    If i = 1 Then
                        strInsert.Append(txtUDF2.Text.Trim)
                    End If
                    If i = 2 Then
                        strInsert.Append(txtUDF3.Text.Trim)
                    End If
                    If i = 3 Then
                        strInsert.Append(txtUDF4.Text.Trim)
                    End If
                    If i = 4 Then
                        strInsert.Append(txtUDF5.Text.Trim)
                    End If
                    If i = 5 Then
                        strInsert.Append(txtUDF6.Text.Trim)
                    End If
                    If i = 6 Then
                        strInsert.Append(txtUDF7.Text.Trim)
                    End If
                    If i = 7 Then
                        strInsert.Append(txtUDF8.Text.Trim)
                    End If
                    If i = 8 Then
                        strInsert.Append(txtUDF9.Text.Trim)
                    End If
                    If i = 9 Then
                        strInsert.Append(txtUDF10.Text.Trim)
                    End If
                    If i = 10 Then
                        strInsert.Append(txtUDF11.Text.Trim)
                    End If
                    If i = 11 Then
                        strInsert.Append(txtUDF12.Text.Trim)
                    End If
                Case "nvarchar", "varchar", "datetime"
                    If i = 1 Then
                        strInsert.Append("'").Append(txtUDF2.Text.Trim).Append("'")
                    End If
                    If i = 2 Then
                        strInsert.Append("'").Append(txtUDF3.Text.Trim).Append("'")
                    End If
                    If i = 3 Then
                        strInsert.Append("'").Append(txtUDF4.Text.Trim).Append("'")
                    End If
                    If i = 4 Then
                        strInsert.Append("'").Append(txtUDF5.Text.Trim).Append("'")
                    End If
                    If i = 5 Then
                        strInsert.Append("'").Append(txtUDF6.Text.Trim).Append("'")
                    End If
                    If i = 6 Then
                        strInsert.Append("'").Append(txtUDF7.Text.Trim).Append("'")
                    End If
                    If i = 7 Then
                        strInsert.Append("'").Append(txtUDF8.Text.Trim).Append("'")
                    End If
                    If i = 8 Then
                        strInsert.Append("'").Append(txtUDF9.Text.Trim).Append("'")
                    End If
                    If i = 9 Then
                        strInsert.Append("'").Append(txtUDF10.Text.Trim).Append("'")
                    End If
                    If i = 10 Then
                        strInsert.Append("'").Append(txtUDF11.Text.Trim).Append("'")
                    End If
                    If i = 11 Then
                        strInsert.Append("'").Append(txtUDF12.Text.Trim).Append("'")
                    End If

                Case Else
                    Return
            End Select
            strInsert.Append(",")
        Next
        strInsert.Remove(strInsert.Length - 1, 1)
        strInsert.Append(")")
        Debug.WriteLine(strInsert.ToString)
        Dim iRet = SqlHelper.ExecuteInsertId(strInsert.ToString, hidTableName.Value)
        If iRet > 0 Then
            txtUDF1.Text = iRet
            BuildUDF2()
        End If
    End Sub

    Private Sub gvUDF_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvUDF.PageIndexChanging
        If e.NewPageIndex >= 0 And e.NewPageIndex < gvUDF.PageCount Then
            gvUDF.PageIndex = e.NewPageIndex
            BuildUDF2()
        End If
    End Sub

    Private Sub gvUDF_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvUDF.RowCommand
        Dim iColumn As Integer
        Select Case e.CommandName
            Case "select"
                txtUDF1.Text = gvUDF.Rows(e.CommandArgument).Cells(1).Text
                txtUDF2.Text = gvUDF.Rows(e.CommandArgument).Cells(2).Text
                txtUDF3.Text = gvUDF.Rows(e.CommandArgument).Cells(3).Text
                iColumn = gvUDF.Rows(e.CommandArgument).Cells.Count - 1
                If iColumn > 1 Then
                    txtUDF1.Text = gvUDF.Rows(e.CommandArgument).Cells(1).Text
                    txtUDF2.Text = gvUDF.Rows(e.CommandArgument).Cells(2).Text
                End If
                If iColumn > 2 Then
                    txtUDF3.Text = gvUDF.Rows(e.CommandArgument).Cells(3).Text
                End If
                If iColumn > 3 Then
                    txtUDF4.Text = gvUDF.Rows(e.CommandArgument).Cells(4).Text
                End If
                If iColumn > 4 Then
                    txtUDF5.Text = gvUDF.Rows(e.CommandArgument).Cells(5).Text
                End If
                If iColumn > 5 Then
                    txtUDF6.Text = gvUDF.Rows(e.CommandArgument).Cells(6).Text
                End If
                If iColumn > 6 Then
                    txtUDF7.Text = gvUDF.Rows(e.CommandArgument).Cells(7).Text
                End If
                If iColumn > 7 Then
                    txtUDF8.Text = gvUDF.Rows(e.CommandArgument).Cells(8).Text
                End If
                If iColumn > 8 Then
                    txtUDF9.Text = gvUDF.Rows(e.CommandArgument).Cells(9).Text
                End If
                If iColumn > 9 Then
                    txtUDF10.Text = gvUDF.Rows(e.CommandArgument).Cells(10).Text
                End If
                If iColumn > 10 Then
                    txtUDF11.Text = gvUDF.Rows(e.CommandArgument).Cells(11).Text
                End If
                If iColumn > 11 Then
                    txtUDF12.Text = gvUDF.Rows(e.CommandArgument).Cells(12).Text
                End If
        End Select
    End Sub

    Protected Sub btnUDFDel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUDFDel.Click
        Dim strSql As String
        Dim iRet As Integer
        If txtUDF1.Text.Trim = "" Then
            Return
        End If
        strSql = "DELETE FROM " & hidTableName.Value & " WHERE IDENT=" & txtUDF1.Text.Trim
        iRet = SqlHelper.ExecuteSql(strSql)
        If iRet > 0 Then
            txtUDF1.Text = ""
            BuildUDF2()
        End If
    End Sub
End Class